BigQuery でテーブル名やカラム名などの更新できない項目を変更するにはどうすればいいか確認してみた

こんにちは、みかみです。

多くのデータベースサービスでは、ALTER TABLE 文でテーブル名やカラム名、データ型の変更、カラムの追加や削除などの変更が可能です。

• ALTER TABLE ｜ Amazon Redshift ドキュメント
• ALTER TABLE ｜ PostgreSQL 9.6.5文書
• 13.1.7 ALTER TABLE 構文 ｜ MySQL 5.6 リファレンスマニュアル

BigQuery でも ALTER TABLE 構文は使えますが、更新できるのはラベルや有効期限など一部のプロパティに限られ、テーブル名やカラム名などの更新はできません。

• ALTER TABLE SET OPTIONS ステートメント ｜ BigQuery ドキュメント
• テーブル プロパティの更新 ｜ BigQuery ドキュメント

では、BigQuery のテーブルでは、どの項目が更新できるのでしょうか？ また、更新できない項目を変更したい場合、どうすれば良いのでしょうか？

やりたいこと

• BigQuery のテーブル「更新」処理で変更可能な項目はどれか確認したい
• BigQuery で「更新」できないテーブルプロパティを変更したい場合、どうすればよいのか知りたい

前提

BigQuery Python クライアントライブラリが実行できる環境は準備済みです。

また、クライアントライブラリ実行時に使用するサービスアカウントには、各処理の実行するために必要なロールを付与済みです。

BigQuery テーブルの更新可能なプロパティ

ドキュメントによると、BigQuery では、テーブルの以下の項目が更新が可能だそうです。

• 説明
• 有効期限
• スキーマ定義
• ラベル

• テーブル プロパティの更新 ｜ BigQuery ドキュメント

Python クライアントライブラリの update_table を使用する場合、以下のプロパティは更新可能です。

• description, friendly_name
• expires, partition_expiration
• schema
• labels

• update_table ｜ Python Client for Google BigQuery

上記項目が Python クライアントライブラリ経由で実際に update できることを確認しました。

from google.cloud import bigquery
from datetime import datetime

table_id = 'cm-da-mikami-yuki-258308.dataset_1.table_dogs'
client = bigquery.Client()
table = client.get_table(table_id)
print('description: {}, friendly_name: {}, expires: {}, labels: {}, schema: {}'.format(
    table.description, table.friendly_name, table.expires, table.labels, table.schema))

table.description = 'テーブル更新のテスト'
table.friendly_name = '動作確認'
table.expires = datetime(2021, 1, 1)
table.labels = dict(key_label='ラベル付与')
schema = [
    bigquery.SchemaField("id", "INTEGER", mode="REQUIRED", description='更新できるか確認'),
    bigquery.SchemaField("name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("x", "STRING"),
    bigquery.SchemaField("y", "INTEGER"),
    bigquery.SchemaField("col_add", "BOOLEAN", mode="NULLABLE"),
]
table.schema = schema
table = client.update_table(table, ['description', 'friendly_name', 'expires', 'labels', 'schema'])
print("Updated table: {}".format(table.table_id))

table = client.get_table(table_id)
print('description: {}, friendly_name: {}, expires: {}, labels: {}, schema: {}'.format(
    table.description, table.friendly_name, table.expires, table.labels, table.schema))

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update.py
description: テスト用, friendly_name: None, expires: None, labels: {'type': 'dog'}, schema: [SchemaField('id', 'INTEGER', 'REQUIRED', None, ()), SchemaField('name', 'STRING', 'REQUIRED', None, ()), SchemaField('x', 'STRING', 'NULLABLE', None, ()), SchemaField('y', 'INTEGER', 'NULLABLE', None, ())]
Updated table: table_dogs
description: テーブル更新のテスト, friendly_name: 動作確認, expires: 2021-01-01 00:00:00+00:00, labels: {'key_label': 'ラベル付与', 'type': 'dog'}, schema: [SchemaField('id', 'INTEGER', 'REQUIRED', '更新できるか確認', ()), SchemaField('name', 'STRING', 'NULLABLE', None, ()), SchemaField('x', 'STRING', 'NULLABLE', None, ()), SchemaField('y', 'INTEGER', 'NULLABLE', None, ()), SchemaField('col_add', 'BOOLEAN', 'NULLABLE', None, ())]

また、パーティショニングテーブルの partition_expiration プロパティが更新できることも確認しました。

では、他のテーブルプロパティは本当に更新できないのでしょうか？ また、更新できないプロパティを変更したい場合には、どうすればよいのでしょうか？

テーブルスキーマ定義の更新可能項目

ドキュメントに「スキーマ定義」の更新が可能との記載がありますが、さらに読み進めていくと、更新可能なのはカラム追加とカラムモードの REQUIRED から NULLABLE への更新のみだと分かります。

• テーブル プロパティの更新 ｜ BigQuery ドキュメント
• テーブル スキーマの変更 ｜ BigQuery ドキュメント

GCP 管理コンソールでも GUI でテーブル定義の更新ができますが、更新可能なのは「モード」と「説明」項目、カラムの新規追加のみです。

GUI には テーブルスキーマを JSON フォーマットで入力し「テキストとして編集」できる機能もあります。 JSON フォーマットであればカラム名やデータ型が変更可能かどうか確認してみましたが、新規カラムの追加となり、やはり既存カラムの変更はできませんでした。

また、カラムモードの NULLABLE から REQUIRED への変更はできません。

試しに Python クライアント経由で更新しようとしてみましたが、エラーとなり、やはり更新できませんでした。

from google.cloud import bigquery

table_id = 'cm-da-mikami-yuki-258308.dataset_1.test_table'
client = bigquery.Client()
table = client.get_table(table_id)
print(table.schema)

schema = [
    bigquery.SchemaField("id", "INTEGER"),
    bigquery.SchemaField("value", "STRING", mode="REQUIRED"),
]
table.schema = schema
table = client.update_table(table, ['schema'])
print("Updated table: {}".format(table.table_id))

table = client.get_table(table_id)
print(table.schema)

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_err.py
[SchemaField('id', 'INTEGER', 'REQUIRED', None, ()), SchemaField('value', 'STRING', 'NULLABLE', None, ())]
Traceback (most recent call last):
  File "table_update_err.py", line 14, in <module>
    table = client.update_table(table, ['schema'])
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 863, in update_table
    timeout=timeout,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 556, in _call_api
    return call()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    on_error=on_error,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 184, in retry_target
    return target()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/_http.py", line 423, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.BadRequest: 400 PATCH https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/datasets/dataset_1/tables/test_table: Provided Schema does not match Table cm-da-mikami-yuki-258308:dataset_1.test_table. Field value has changed mode from NULLABLE to REQUIRED

カラム名、データ型の変更とカラムの削除

更新できないカラム名やデータ型を変更、または既存カラムを削除したい場合は、既存テーブルを元に新しいテーブルを作成する必要があります。

• テーブル スキーマの手動変更 ｜ BigQuery ドキュメント

以下のテーブルで、 id カラムのデータ型を STRING に変更し、name カラムのカラム名を name_new に変更、さらに col_add カラムを削除してみます。

from google.cloud import bigquery

client = bigquery.Client()
table_id = 'cm-da-mikami-yuki-258308.dataset_1.table_dogs'
table_dst = client.get_table(table_id)

job_config = bigquery.QueryJobConfig()
job_config.destination = table_dst
job_config.write_disposition = 'WRITE_TRUNCATE'

query = (
    'SELECT CAST(id AS STRING) AS id, name AS name_new FROM dataset_1.table_dogs'
)
job = client.query(query, job_config=job_config)
print("Starting job {}".format(job.job_id))

job.result()
print("Job finished.")

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_mod.py
Starting job 7a20ffcd-1627-4fa7-94bf-e5ddf0e521a3
Job finished.

テーブルデータはそのまま、無事、カラム名、データ型の変更とカラム削除が実行できました。

テーブル名を変更

テーブル名を変更する場合にも、既存テーブルをコピーして新しいテーブルを作成し、元テーブルを削除する必要があります。

• テーブル名の変更 ｜ BigQuery ドキュメント

以下のコードで、新しい名前のテーブルに差し替えることができました。

from google.cloud import bigquery

client = bigquery.Client()
src_table_id = 'cm-da-mikami-yuki-258308.dataset_1.data_sample'
src_table = client.get_table(src_table_id)
table_id = '{}_new'.format(src_table_id)
table = bigquery.Table(table_id)

job = client.copy_table(src_table, table)
print("Starting job {}".format(job.job_id))
job.result()
print("Job finished.")

client.delete_table(src_table)
print("Deleted table {}.".format(src_table.table_id))

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_mod_name.py
Starting job 3d817404-01a9-406d-a362-e89ab57ba1e4
Job finished.
Deleted table data_sample.

CREATE TABLE AS SELECT クエリの実行でも、同じデータを持つテーブルを作成することが可能です。 以下の SQL 実行でもテーブルコピー可能なことが確認できました。

CREATE TABLE cm-da-mikami-yuki-258308.dataset_1.data_sample_copy AS
SELECT * FROM cm-da-mikami-yuki-258308.dataset_1.data_sample

ロケーション、データセットの変更

テーブルのロケーションはデータセットのロケーションに依存するため、テーブルだけを所属するデータセットとは異なるロケーションに変更することはできません。

また、同じロケーションの別データセットにテーブルを移動したい場合には、テーブル名変更同様テーブルコピーで対応できますが、BigQuery ではローケーションをまたいだデータのコピーはできないそうです。

試しに Python クライアントで、テーブルを別ロケーションのデータセットにコピーしようとしてみましたが、Not found: Dataset ... エラーとなり、やはりコピーできませんでした。

• テーブルの制限事項 ｜ BigQuery ドキュメント
• ロケーション間での BigQuery データの移動 ｜ BigQuery ドキュメント

テーブルを別ロケーションのデータセットに変更する場合には、移動先のデータセットに新規テーブルを作成し、元テーブルに格納済みデータを GCS にエクスポート後に新規テーブルにロードする必要があります。

または、データセットごと別ロケーションにコピーすれば、テーブルのロケーションも変更できます。

なお、ドキュメントによると、データセットのコピーは現在ベータ版とのことで、ロケーションなどの制限事項があるそうです。

• データセットのコピー ｜ BigQuery ドキュメント
• ロケーションの制限 ｜ BigQuery ドキュメント

データセットのコピーは、GCP 管理コンソールから簡単に実行できます。

BigQuery 管理画面でコピー元データセットを選択して「データセットをコピー」をクリックし、コピー先データセット情報を入力して「コピー」をクリック。

コピー処理の裏側では BigQuery Data Transfer Service を使っているとドキュメントに記載もありましたが、BigQuery Data Transfer Service のアクセス許可ポップアップが表示されるため、ブラウザでポップアップ禁止にしている場合は、許可設定を追加する必要があります。

データセットコピーの実行状況は、BigQuery 管理画面「転送」メニューから確認できます。

しばらく待っていると、転送完了になりました。

データセットと一緒に、テーブルが別リージョンにコピーされたことが確認できました。

from google.cloud import bigquery

client = bigquery.Client()
src_table_id = 'cm-da-mikami-yuki-258308.dataset_1.data_sample'
src_table = client.get_table(src_table_id)
print('{} -> dataset: {} / location: {}'.format(src_table.table_id, src_table.dataset_id, src_table.location))

dst_table_id = 'cm-da-mikami-yuki-258308.dataset_US.data_sample'
dst_table = client.get_table(dst_table_id)
print('{} -> dataset: {} / location: {}'.format(dst_table.table_id, dst_table.dataset_id, dst_table.location))

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_check_location.py
data_sample -> dataset: dataset_1 / location: asia-northeast1
data_sample -> dataset: dataset_US / location: US

データセットコピー後に転送設定を確認してみると、デイリーでスケジュール実行される設定になっていました。

現在ベータ版とのことなので今後仕様変更される可能性はありますが、現状、特にデイリーでコピーする必要がない場合には、スケジュール実行の停止または転送設定を削除する必要があるのでご注意ください。

パーティショニング関連のプロパティを更新

Python クライアントライブラリの Table クラスには、以下のパーティションテーブル関連のプロパティがあります。

• partitioning_type
• range_partitioning
• time_partitioning
• require_partition_filter

これらのプロパティは本当に update できないのか、確認してみました。

from google.cloud import bigquery

table_id = 'cm-da-mikami-yuki-258308.dataset_1.pos_partition_val_copy'
client = bigquery.Client()
table = client.get_table(table_id)
print('partitioning_type: {}, range_partitioning: {}, time_partitioning: {}, require_partition_filter: {}'.format(
    table.partitioning_type, table.range_partitioning, table.time_partitioning, table.require_partition_filter))

table.partitioning_type = 'DAY'
range = bigquery.table.PartitionRange(end=5000, interval=1000, start=1)
table.range_partitioning = bigquery.table.RangePartitioning(range_=range, field='price_avg')
table.time_partitioning = bigquery.table.TimePartitioning(type_ ='DAY')
table.require_partition_filter = True
table = client.update_table(table, ['partitioning_type', 'range_partitioning', 'time_partitioning', 'require_partition_filter'])
table = client.update_table(table, ['partitioning_type', 'time_partitioning', 'require_partition_filter'])
print("Updated table: {}".format(table.table_id))

table = client.get_table(table_id)
print('partitioning_type: {}, range_partitioning: {}, time_partitioning: {}, require_partition_filter: {}'.format(
    table.partitioning_type, table.range_partitioning, table.time_partitioning, table.require_partition_filter))

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_partition.py
partitioning_type: None, range_partitioning: RangePartitioning(field='JANCD', range_=PartitionRange(end=5000000000000, interval=100000000, start=4000000000000)), time_partitioning: None, require_partition_filter: None
Traceback (most recent call last):
  File "table_update_partition.py", line 15, in <module>
    table = client.update_table(table, ['partitioning_type', 'range_partitioning', 'time_partitioning', 'require_partition_filter'])
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 852, in update_table
    partial = table._build_resource(fields)
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/table.py", line 883, in _build_resource
    return _helpers._build_resource_from_properties(self, filter_fields)
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/_helpers.py", line 663, in _build_resource_from_properties
    raise ValueError("No property %s" % filter_field)
ValueError: No property range_partitioning

range_partitioning は指定できないと怒られているようです。。

クライアントライブラリのコードを確認してみます。

（省略）
class Client(ClientWithProject):
（省略）
    def update_table(self, table, fields, retry=DEFAULT_RETRY, timeout=None):
（省略）
        partial = table._build_resource(fields)
        if table.etag is not None:
            headers = {"If-Match": table.etag}
        else:
            headers = None
        api_response = self._call_api(
            retry,
            method="PATCH",
            path=table.path,
            data=partial,
            headers=headers,
            timeout=timeout,
        )
        return Table.from_api_repr(api_response)
（省略）

• 抜粋：python-bigquery/google/cloud/bigquery/client.py ｜ GitHub

（省略）
class Table(object):
（省略）
    _PROPERTY_TO_API_FIELD = {
        "friendly_name": "friendlyName",
        "expires": "expirationTime",
        "time_partitioning": "timePartitioning",
        "partitioning_type": "timePartitioning",
        "partition_expiration": "timePartitioning",
        "view_use_legacy_sql": "view",
        "view_query": "view",
        "external_data_configuration": "externalDataConfiguration",
        "encryption_configuration": "encryptionConfiguration",
        "require_partition_filter": "requirePartitionFilter",
    }
（省略）
    @range_partitioning.setter
    def range_partitioning(self, value):
        resource = value
        if isinstance(value, RangePartitioning):
            resource = value._properties
        elif value is not None:
            raise ValueError(
                "Expected value to be RangePartitioning or None, got {}.".format(value)
            )
        self._properties["rangePartitioning"] = resource
（省略）
    def _build_resource(self, filter_fields):
        """Generate a resource for ``update``."""
        return _helpers._build_resource_from_properties(self, filter_fields)
（省略）

• 抜粋：python-bigquery/google/cloud/bigquery/table.py ｜ GitHub

（省略）
def _build_resource_from_properties(obj, filter_fields):
（省略）
    partial = {}
    for filter_field in filter_fields:
        api_field = obj._PROPERTY_TO_API_FIELD.get(filter_field)
        if api_field is None and filter_field not in obj._properties:
            raise ValueError("No property %s" % filter_field)
        elif api_field is not None:
            partial[api_field] = obj._properties.get(api_field)
        else:
            # allows properties that are not defined in the library
            # and properties that have the same name as API resource key
            partial[filter_field] = obj._properties[filter_field]

    return partial
（省略）

• 抜粋：python-bigquery/google/cloud/bigquery/_helpers.py ｜ GitHub

Table クラスで定義済みの _PROPERTY_TO_API_FIELD または Table._properties にない項目を指定した場合のエラーが出ているようです。また、パーティショニングレンジのプロパティは、Table._properties には rangePartitioning という名前で定義されているようです。。

更新項目名を修正して再実行してみます。

（省略）
table.partitioning_type = 'DAY'
range = bigquery.table.PartitionRange(end=5000, interval=1000, start=1)
table.range_partitioning = bigquery.table.RangePartitioning(range_=range, field='price_avg')
table.time_partitioning = bigquery.table.TimePartitioning(type_ ='DAY')
table.require_partition_filter = True
#table = client.update_table(table, ['partitioning_type', 'range_partitioning', 'time_partitioning', 'require_partition_filter'])
table = client.update_table(table, ['partitioning_type', 'rangePartitioning', 'time_partitioning', 'require_partition_filter'])
print("Updated table: {}".format(table.table_id))
（省略）

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_partition.py
partitioning_type: None, range_partitioning: RangePartitioning(field='JANCD', range_=PartitionRange(end=5000000000000, interval=100000000, start=4000000000000)), time_partitioning: None, require_partition_filter: True
Traceback (most recent call last):
  File "table_update_partition.py", line 15, in <module>
    table = client.update_table(table, ['partitioning_type', 'rangePartitioning', 'time_partitioning', 'require_partition_filter'])
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 863, in update_table
    timeout=timeout,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 556, in _call_api
    return call()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    on_error=on_error,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 184, in retry_target
    return target()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/_http.py", line 423, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.BadRequest: 400 PATCH https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/datasets/dataset_1/tables/pos_partition_val_copy: Cannot change partitioning/clustering spec for a partitioned/clustered table.

ValueError は出なくなりましたが、BigQuery API で Cannot change partitioning/clustering spec for a partitioned/clustered table. エラーとなりました。 range_partitioning はやはり更新できないようです。

では、range_partitioning の更新箇所だけコメントアウトして再度実行してみます。

（省略）
table.partitioning_type = 'DAY'
#range = bigquery.table.PartitionRange(end=5000, interval=1000, start=1)
#table.range_partitioning = bigquery.table.RangePartitioning(range_=range, field='price_avg')
table.time_partitioning = bigquery.table.TimePartitioning(type_ ='DAY')
table.require_partition_filter = True
#table = client.update_table(table, ['partitioning_type', 'range_partitioning', 'time_partitioning', 'require_partition_filter'])
table = client.update_table(table, ['partitioning_type', 'time_partitioning', 'require_partition_filter'])
print("Updated table: {}".format(table.table_id))
（省略）

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_partition.py
partitioning_type: None, range_partitioning: RangePartitioning(field='JANCD', range_=PartitionRange(end=5000000000000, interval=100000000, start=4000000000000)), time_partitioning: None, require_partition_filter: None
Updated table: pos_partition_val_copy
partitioning_type: None, range_partitioning: RangePartitioning(field='JANCD', range_=PartitionRange(end=5000000000000, interval=100000000, start=4000000000000)), time_partitioning: None, require_partition_filter: True

実際に更新できたのは require_partition_filter だけで、partitioning_type と time_partitioning の値は更新されていないようですが、特にエラーは発生しませんでした。

partitioning_type と time_partitioning が更新されなかったのは、既存テーブルのパーティション設定内容または更新内容の問題かと思い、他のパーティションテーブルでも確認してみました。

パーティションテーブルをパーティショニングなしに更新しようとすると、Cannot change partitioned table to non partitioned table. と怒られました。。

（省略）
table.partitioning_type = None
table.time_partitioning = None
table = client.update_table(table, ['partitioning_type', 'time_partitioning'])
print("Updated table: {}".format(table.table_id))
（省略）

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_partition_2.py
partitioning_type: DAY, time_partitioning: TimePartitioning(field=first_date,type=DAY)
Traceback (most recent call last):
  File "table_update_partition_2.py", line 12, in <module>
    table = client.update_table(table, ['partitioning_type', 'time_partitioning'])
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 863, in update_table
    timeout=timeout,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 556, in _call_api
    return call()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    on_error=on_error,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 184, in retry_target
    return target()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/_http.py", line 423, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.BadRequest: 400 PATCH https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/datasets/dataset_1/tables/pos_partition_date_copy: Cannot change partitioned table to non partitioned table.

逆に、通常のテーブルをデータロード日時でパーティショニングするように更新しようとしてみても、Cannot convert non partitioned table to partitioned table. でエラーです。。

（省略）
table.partitioning_type = 'DAY'
table.time_partitioning = bigquery.table.TimePartitioning(type_ ='DAY')
table = client.update_table(table, ['partitioning_type', 'time_partitioning'])
print("Updated table: {}".format(table.table_id))
（省略）

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_partition_3.py
partitioning_type: None, time_partitioning: None
Traceback (most recent call last):
  File "table_update_partition_3.py", line 11, in <module>
    table = client.update_table(table, ['partitioning_type', 'time_partitioning'])
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 863, in update_table
    timeout=timeout,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 556, in _call_api
    return call()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    on_error=on_error,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 184, in retry_target
    return target()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/_http.py", line 423, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.BadRequest: 400 PATCH https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/datasets/dataset_1/tables/data_sample_copy: Cannot convert non partitioned table to partitioned table.

現状 partitioning_type に指定できるのは、DAY のみなので、partitioning_type は更新できないということになります。

では time_partitioning プロパティも本当に更新できないのか、ロード日時パーティショニング指定のみのパーティションテーブルに、パーティションフィールドの追加を試みてみます。

（省略）
table.time_partitioning = bigquery.table.TimePartitioning(type_='DAY', field='first_date')
table = client.update_table(table, ['time_partitioning'])
print("Updated table: {}".format(table.table_id))
（省略）

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_partition_4.py
time_partitioning: TimePartitioning(type=DAY)
Traceback (most recent call last):
  File "table_update_partition_4.py", line 10, in <module>
    table = client.update_table(table, ['time_partitioning'])
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 863, in update_table
    timeout=timeout,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 556, in _call_api
    return call()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    on_error=on_error,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 184, in retry_target
    return target()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/_http.py", line 423, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.BadRequest: 400 PATCH https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/datasets/dataset_1/tables/pos_partition_loadtime_copy: Cannot change partitioning/clustering spec for a partitioned/clustered table.

Cannot change partitioning/clustering spec for a partitioned/clustered table. とのことで、やはり更新できませんでした。。

結果、パーティション関連のプロパティでは、require_partition_filter の更新は可能でした。

Etag 値って更新できる？！

Python クライアントライブラリのドキュメントに、以下の記載がありました。

If table.etag is not None, the update will only succeed if the table on the server has the same ETag. Thus reading a table with get_table, changing its fields, and then passing it to update_table will ensure that the changes will only be saved if no modifications to the table occurred since the read.

• 抜粋：update_table ｜ Python Client for Google BigQuery

翻訳サイトで日本語にしてみた結果がこちら。

table.etagがNoneでない場合、サーバー上のテーブルに同じETagがある場合にのみ更新が成功します。したがって、get_tableでテーブルを読み取り、そのフィールドを変更してからupdate_tableに渡すと、読み取り以降にテーブルに変更が加えられなかった場合にのみ変更が保存されます。

他テーブルの Etag と同じ値であれば更新できるってこと？！（よく分かりませぬ。。

Etag とは、一般的に更新を管理する識別子のはずで、クライアントから更新できたらまずいはず。。

試してみます。

from google.cloud import bigquery

client = bigquery.Client()
src_table_id = 'cm-da-mikami-yuki-258308.dataset_1.data_sample'
table_src = client.get_table(src_table_id)
print('{} -> etag: {}'.format(table_src.table_id, table_src.etag))
table_id = 'cm-da-mikami-yuki-258308.dataset_1.data_sample_copy'
table = client.get_table(table_id)
print('{} -> etag: {}'.format(table.table_id, table.etag))

table.etag = table_src.etag
table = client.update_table(table, ['etag'])
print("Updated table: {}".format(table.table_id))

table = client.get_table(table_id)
print('{} -> etag: {}'.format(table.table_id, table.etag))

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_etag.py
data_sample -> etag: PlIXbi6QsPP8PhIRTgxXew==
data_sample_copy -> etag: kQn8xzuqtMpwWJTCWCzLmA==
Traceback (most recent call last):
  File "table_update_etag.py", line 11, in <module>
    table.etag = table_src.etag
AttributeError: can't set attribute

クライアントライブラリのコード確認したところ、確かに、Table クラスの etag プロパティには setter がありません。やっぱ更新できませんよね。

もう一度、よく考えて英語を解読してみます。

If table.etag is not None, the update will only succeed if the table on the server has the same ETag.

もし Etag が None じゃなければ、サーバー上のテーブルが同じ Etag の場合、更新は成功？

... that the changes will only be saved if no modifications to the table occurred since the read.

もしテーブル読み込み時から修正が加えられてなければ、変更は保存されるでしょう？

• 抜粋：update_table ｜ Python Client for Google BigQuery

あ、更新処理の整合性のことを言ってるのか。。更新対象のテーブルインスタンスを取得した後、別のクライアントから更新が加わった場合、update_table は失敗すると。

実際に確認してみます。

from google.cloud import bigquery
import time

client = bigquery.Client()
table_id = 'cm-da-mikami-yuki-258308.dataset_1.data_sample_copy'
table = client.get_table(table_id)
print('{} -> description: {}'.format(table.table_id, table.description))

time.sleep(60)

table.description = '更新テスト！'
table = client.update_table(table, ['description'])
print("Updated table: {}".format(table.table_id))

table = client.get_table(table_id)
print('{} -> description: {}'.format(table.table_id, table.description))

get_table してから update_table までの間に sleep を入れ、その隙に GCP 管理コンソールから description を更新しました。

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_etag_2.py
data_sample_copy -> description: 更新テスト
Traceback (most recent call last):
  File "table_update_etag_2.py", line 12, in <module>
    table = client.update_table(table, ['description'])
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 863, in update_table
    timeout=timeout,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 556, in _call_api
    return call()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    on_error=on_error,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 184, in retry_target
    return target()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/_http.py", line 423, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.PreconditionFailed: 412 PATCH https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/datasets/dataset_1/tables/data_sample_copy: Precondition check failed.

想定通り、更新エラーとなりました。（自分の英語力の無さに泣ける。。。

その他のプロパティで更新可能なものは？

クライアントライブラリのコードを確認すると、他にも、以下のプロパティには setter がありました。 ということは、更新可能なのでしょうか？

• encryption_configuration
• clustering_fields
• view_query
• view_use_legacy_sql
• external_data_configuration

• python-bigquery/google/cloud/bigquery/table.py ｜ GitHub
• google.cloud.bigquery.table.Table ｜ Python Client for Google BigQuery

clustering_fields 以外のプロパティは _PROPERTY_TO_API_FIELD でも更新対象項目として定義されているので更新できそうなのですが、実際に確認してみます。

まずは encryption_configuration（データの暗号化鍵キー設定）を変更してみます。

デフォルトキーのテーブルに、KMS のキーを設定しようとしてみると。

from google.cloud import bigquery

table_id = 'cm-da-mikami-yuki-258308.dataset_1.data_sample_copy'
client = bigquery.Client()
table = client.get_table(table_id)
print('encryption_configuration: {}'.format(table.encryption_configuration))

table.encryption_configuration = bigquery.EncryptionConfiguration('projects/cm-da-mikami-yuki-258308/locations/asia-northeast1/keyRings/test-mikami/cryptoKeys/test-cm-mikami')
table = client.update_table(table, ['encryption_configuration'])
print("Updated table: {}".format(table.table_id))

table = client.get_table(table_id)
print('encryption_configuration: {}'.format(table.encryption_configuration))

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_kms.py
encryption_configuration: None
Traceback (most recent call last):
  File "table_update_kms.py", line 9, in <module>
    table = client.update_table(table, ['encryption_configuration'])
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 863, in update_table
    timeout=timeout,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 556, in _call_api
    return call()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    on_error=on_error,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 184, in retry_target
    return target()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/_http.py", line 423, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.BadRequest: 400 PATCH https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/datasets/dataset_1/tables/data_sample_copy: Changing from Default to Cloud KMS encryption key and back must be done via table.copy job. https://cloud.google.com/bigquery/docs/customer-managed-encryption#change_to_kms

Changing from Default to Cloud KMS encryption key and back must be done via table.copy job. とのことで、デフォルトキーから KMS キー、またはその逆の更新はできないとのことです。

では、すでに KMS キーを設定しているテーブルに対して、別のキーに更新できるか確認してみます。

（省略）
table_id = 'cm-da-mikami-yuki-258308.dataset_1.table_kms'
（省略）
#table.encryption_configuration = bigquery.EncryptionConfiguration('projects/cm-da-mikami-yuki-258308/locations/asia-northeast1/keyRings/test-mikami/cryptoKeys/test-cm-mikami')
table.encryption_configuration = bigquery.EncryptionConfiguration('projects/cm-da-mikami-yuki-258308/locations/asia-northeast1/keyRings/test-mikami/cryptoKeys/mikami-for-test-update')
table = client.update_table(table, ['encryption_configuration'])
print("Updated table: {}".format(table.table_id))
（省略）

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_kms.py
encryption_configuration: EncryptionConfiguration(projects/cm-da-mikami-yuki-258308/locations/asia-northeast1/keyRings/test-mikami/cryptoKeys/test-cm-mikami)
Updated table: table_kms
encryption_configuration: EncryptionConfiguration(projects/cm-da-mikami-yuki-258308/locations/asia-northeast1/keyRings/test-mikami/cryptoKeys/mikami-for-test-update)

今度は無事更新できました。

続いて external_data_configuration（外部テーブルのデータソース設定）を変更してみます。

既存の通常テーブルを外部テーブルに変更しようとしてみます。

from google.cloud import bigquery

table_id = 'cm-da-mikami-yuki-258308.dataset_1.data_sample_copy'
client = bigquery.Client()
table = client.get_table(table_id)
print('external_data_configuration: {}'.format(table.external_data_configuration))

external_config = bigquery.ExternalConfig('CSV')
external_config.source_uris = [
    'gs://test-mikami/yob1980.txt'
]
table.external_data_configuration = external_config
table = client.update_table(table, ['external_data_configuration'])
print("Updated table: {}".format(table.table_id))

table = client.get_table(table_id)
print('external_data_configuration: {}'.format(table.external_data_configuration))

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_external.py
external_data_configuration: None
Traceback (most recent call last):
  File "table_update_external.py", line 13, in <module>
    table = client.update_table(table, ['external_data_configuration'])
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 863, in update_table
    timeout=timeout,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 556, in _call_api
    return call()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    on_error=on_error,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 184, in retry_target
    return target()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/_http.py", line 423, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.BadRequest: 400 PATCH https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/datasets/dataset_1/tables/data_sample_copy: Cannot set an ExternalDataConfiguration for cm-da-mikami-yuki-258308:dataset_1.data_sample_copy because it is not of type External.

Cannot set an ExternalDataConfiguration for ... because it is not of type External. とのことで、外部テーブルではないテーブルを外部テーブルに変更することはできないとのこと。

では、外部テーブルのソースデータを更新してみます。

（省略）
table_id = 'cm-da-mikami-yuki-258308.dataset_1.table_external'
（省略）
external_config = bigquery.ExternalConfig('CSV')
external_config.source_uris = [
#    'gs://test-mikami/yob1980.txt'
    'gs://test-mikami/data_test/yob2010.txt'
]
table.external_data_configuration = external_config
table = client.update_table(table, ['external_data_configuration'])
print("Updated table: {}".format(table.table_id))
（省略）

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_external.py
external_data_configuration: ['gs://test-mikami/yob1980.txt']
Updated table: table_external
external_data_configuration: ['gs://test-mikami/data_test/yob2010.txt']

今度はちゃんと更新できました。

最後に、ビュー関連のプロパティを更新しようとしてみます。

from google.cloud import bigquery

table_id = 'cm-da-mikami-yuki-258308.dataset_1.view_dogs_2'
client = bigquery.Client()
table = client.get_table(table_id)
print('view_use_legacy_sql: {}, view_query: {}'.format(table.view_use_legacy_sql, table.view_query))

table.view_use_legacy_sql = True
table.view_query = 'SELECT name FROM [cm-da-mikami-yuki-258308:dataset_1.view_dogs]'
table = client.update_table(table, ['view_use_legacy_sql', 'view_query'])
print("Updated table: {}".format(table.table_id))

table = client.get_table(table_id)
print('view_use_legacy_sql: {}, view_query: {}'.format(table.view_use_legacy_sql, table.view_query))

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_view.py
view_use_legacy_sql: False, view_query: SELECT name FROM cm-da-mikami-yuki-258308.dataset_1.view_dogs
Traceback (most recent call last):
  File "table_update_view.py", line 10, in <module>
    table = client.update_table(table, ['view_use_legacy_sql', 'view_query'])
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 863, in update_table
    timeout=timeout,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 556, in _call_api
    return call()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    on_error=on_error,
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/api_core/retry.py", line 184, in retry_target
    return target()
  File "/home/ec2-user/test_update/lib64/python3.7/site-packages/google/cloud/_http.py", line 423, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.BadRequest: 400 PATCH https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/datasets/dataset_1/tables/view_dogs_2: Cannot reference a standard SQL view in a legacy SQL query.

Cannot reference a standard SQL view in a legacy SQL query. とのことで、レガシー SQL から標準 SQL への変更はできないとのこと。。

では逆に、レガシーSQL 定義のビューを標準 SQL 定義に変更することは可能なのでしょうか？

（省略）
table_id = 'cm-da-mikami-yuki-258308.dataset_1.view_sample'
（省略）
#table.view_use_legacy_sql = True
#table.view_query = 'SELECT name FROM [cm-da-mikami-yuki-258308:dataset_1.view_dogs]'
table.view_use_legacy_sql = False
table.view_query = 'SELECT DISTINCT _col_2 FROM cm-da-mikami-yuki-258308.dataset_1.data_sample'
table = client.update_table(table, ['view_use_legacy_sql', 'view_query'])
print("Updated table: {}".format(table.table_id))
（省略）

(test_update) [ec2-user@ip-10-0-43-239 test_update]$ python table_update_view.py
view_use_legacy_sql: True, view_query: SELECT _col_2 FROM [cm-da-mikami-yuki-258308:dataset_1.data_sample] GROUP BY _col_2
Updated table: view_sample
view_use_legacy_sql: False, view_query: SELECT DISTINCT _col_2 FROM cm-da-mikami-yuki-258308.dataset_1.data_sample

問題なく更新することができました。

まとめ（所感）

BigQuery のテーブルを Python クライアントライブラリ経由で「更新」する場合、以下のテーブルプロパティが更新可能でした。

• description
• friendly_name
• expires
• partition_expiration
• schema
• labels
• require_partition_filter
• encryption_configuration
• external_data_configuration
• view_query
• view_use_legacy_sql

BigQuery の「更新」インターフェースで更新できる項目は限定的で、さらに更新可能な項目でも更新値は制限されます。

実際にどの項目をどの値で更新できるか詳細に記載されたドキュメントは、確認した限りでは発見できませんでした。。クライアントライブラリのソースコードを見たり、実際に試してみるのがよさそうです。

この挙動は、BigQuery が他のインメモリデータベースとは異なる管理構造をとっているためと推測します。BigQuery がどんなアーキテクチャなのか考えてみるのも面白そうです。

BigQuery でテーブルプロパティを更新したい場合、既存テーブルを「更新」するという考え方だけではなく、テーブルコピーや SQL クエリ実行などを使って、テーブルを「差し替える」という概念を持つ必要もあるのかと思いました。

参考

• テーブル プロパティの更新 ｜ BigQuery ドキュメント
• テーブル スキーマの変更 ｜ BigQuery ドキュメント
• テーブル スキーマの手動変更 ｜ BigQuery ドキュメント
• テーブル名の変更 ｜ BigQuery ドキュメント
• Python Client for Google BigQuery
• update_table ｜ Python Client for Google BigQuery
• googleapis/python-bigquery ｜ GitHub